using System;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Generic;
using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesReport;
using RBAC.Read.Api.Application.Dto.ERP.SalesReport;
using static RBAC.ErrorCode.APIEnums;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesReport
{
    /// <summary>
    /// 发货通知明细表 查询处理器
    /// </summary>
    public class GetDeliveryNoticeDetailListHandler : IRequestHandler<GetDeliveryNoticeDetailListCommand, APIResult<GetDeliveryNoticeDetailListDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetDeliveryNoticeDetailListHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetDeliveryNoticeDetailListDto>> Handle(GetDeliveryNoticeDetailListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var conn = _dbConnectionFactory.CreateConnection();

                var where = new List<string>
                {
                    "dn.IsDeleted = 0",
                    "(dni.IsDeleted = 0 OR dni.Id IS NULL)"
                };
                var p = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.SalesOrderCode))
                {
                    // 同时支持按 销售订单号 与 发货通知单号 模糊查询
                    // 例如：输入 XSFH000010 属于发货单号（dn.Bill_code），也应能匹配
                    where.Add("(dn.Sales_order_code LIKE @SalesOrBillCode OR dn.Bill_code LIKE @SalesOrBillCode OR so.OrderCode LIKE @SalesOrBillCode)");
                    p.Add("@SalesOrBillCode", $"%{request.SalesOrderCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    where.Add("c.CustomerName LIKE @CustomerName");
                    p.Add("@CustomerName", $"%{request.CustomerName.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    where.Add("(dni.Material_name LIKE @MaterialName OR m.MaterName LIKE @MaterialName)");
                    p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
                }
                if (request.StartDate.HasValue)
                {
                    where.Add("dn.Bill_date >= @StartDate");
                    p.Add("@StartDate", request.StartDate.Value.Date);
                }
                if (request.EndDate.HasValue)
                {
                    where.Add("dn.Bill_date <= @EndDate");
                    p.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
                }

                var whereClause = where.Count > 0 ? ("WHERE " + string.Join(" AND ", where)) : string.Empty;

                var pageIndex = request.PageIndex <= 0 ? 1 : request.PageIndex;
                var pageSize = request.PageSize <= 0 ? 20 : request.PageSize;
                var offset = (pageIndex - 1) * pageSize;

                // MySQL: use separate COUNT and data queries (no CTE / OFFSET FETCH)
                var countSql = $@"
SELECT COUNT(1) AS TotalCount
FROM (
  SELECT 1
  FROM Delivery_notice dn
  LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
  LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
  LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND (dni.IsDeleted = 0 OR dni.Id IS NULL)
  LEFT JOIN Mater m ON (dni.Material_id = m.Id OR (dni.Material_code IS NOT NULL AND dni.Material_code = m.MaterCode)) AND m.IsDeleted = 0
  LEFT JOIN SalesOrder so ON so.OrderCode = dn.Sales_order_code AND so.IsDeleted = 0
  LEFT JOIN SalesOrderDetail sod ON sod.SalesOrderId = so.Id AND m.Id IS NOT NULL AND sod.MaterId = m.Id AND sod.IsDeleted = 0
  {whereClause}
) t";

                var dataSql = $@"
SELECT 
  dn.Bill_code AS BillCode,
  c.CustomerName AS CustomerName,
  u.UserName AS SalesmanName,
  dn.Bill_date AS BillDate,
  COALESCE(dni.Material_code, m.MaterCode) AS MaterialCode,
  COALESCE(dni.Material_name, m.MaterName) AS MaterialName,
  sod.Num AS OrderQty,
  dni.Qty AS DeliveryQty
FROM Delivery_notice dn
LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND (dni.IsDeleted = 0 OR dni.Id IS NULL)
LEFT JOIN Mater m ON (dni.Material_id = m.Id OR (dni.Material_code IS NOT NULL AND dni.Material_code = m.MaterCode)) AND m.IsDeleted = 0
LEFT JOIN SalesOrder so ON so.OrderCode = dn.Sales_order_code AND so.IsDeleted = 0
LEFT JOIN SalesOrderDetail sod ON sod.SalesOrderId = so.Id AND m.Id IS NOT NULL AND sod.MaterId = m.Id AND sod.IsDeleted = 0
{whereClause}
ORDER BY dn.Bill_date DESC, dn.Id DESC, dni.Id ASC
LIMIT @PageSize OFFSET @Offset;";

                p.Add("@Offset", offset);
                p.Add("@PageSize", pageSize);

                var totalCount = await conn.ExecuteScalarAsync<int>(countSql, p);
                var items = (await conn.QueryAsync<DeliveryNoticeDetailRowDto>(dataSql, p)).ToList();

                // Compute UndeliveredQty
                foreach (var r in items)
                {
                    var order = r.OrderQty ?? 0m;
                    var delivered = r.DeliveryQty ?? 0m;
                    r.UndeliveredQty = Math.Max(0m, order - delivered);
                }

                return new APIResult<GetDeliveryNoticeDetailListDto>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = new GetDeliveryNoticeDetailListDto
                    {
                        TotalCount = totalCount,
                        Items = items
                    }
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetDeliveryNoticeDetailListDto>
                {
                    Code = Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
